﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DTO;

namespace DAO
{
    public class TongHopDAO
    {
         #region usp_tinchuyentheoma
            public static List<TongHopDTO> SelectSoLuongChuyentheoma(String machuyen,ref int tong)
            {
                List<TongHopDTO> lst = new List<TongHopDTO>();

                SqlTransaction tran;
                SqlConnection conn = DataProvider.ConnectDB();
                SqlCommand command = conn.CreateCommand(); //tao doi tuong command
                tran = conn.BeginTransaction(IsolationLevel.ReadCommitted, "ChuyenXetheoma");//start tran tren connection nay
                command.Connection = conn;
                command.Transaction = tran;

                command.CommandTimeout = 2000;
                //ma chuyen ton tai?
                command.CommandText = "select * from LICHDI where MACHUYEN=@ma";
                SqlParameter pa = new SqlParameter("@ma", SqlDbType.VarChar);
                pa.Value = machuyen;
                command.Parameters.Add(pa);
                SqlDataReader rd = command.ExecuteReader();
                if (rd.HasRows == false)
                {
                    rd.Close();
                    tran.Rollback();
                    throw new Exception("Chuyến Xe này hiện chưa có lịch đi");
                }
                command.Parameters.Clear();
                rd.Dispose();
                command.Dispose();

                command.CommandText = "select count(MACHUYEN) from LICHDI where MACHUYEN=@ma ";
                command.Parameters.Add(pa);
                int tt = (int)command.ExecuteScalar();//gia tri output
                tong = tt;
               // System.Threading.Thread.Sleep(10000);
                rd.Dispose();
                command.Dispose();
                command.Parameters.Clear();
                command.CommandText = " select ld.NGAYDI,cx.GIODI,cx.GIODEN,cx.BENXEDI,cx.BENXEDEN,ld.MALOAI,ld.SOHIEU,lx.TRONGTAI,ld.TTHIENTAI from LICHDI ld, CHUYENXE cx, LOAIXE lx where cx.MACHUYEN=ld.MACHUYEN and ld.MALOAI=lx.MALOAI and cx.MACHUYEN=@ma";
                command.Parameters.Add(pa);
                SqlDataReader sa = command.ExecuteReader();
                while (sa.Read())
                {
                    TongHopDTO cx = new TongHopDTO();
                    cx.Ngaydi = DateTime.Parse(sa["ngaydi"].ToString());
                    cx.GIODI = DateTime.Parse(sa["giodi"].ToString());
                    cx.GIODEN = DateTime.Parse(sa["gioden"].ToString());
                    cx.BENXEDI = sa["BENXEDI"].ToString();
                    cx.BENXEDEN = sa["BENXEDEN"].ToString();
                    cx.Maloai = sa["MALOAI"].ToString();
                    cx.Sohieu = sa["sohieu"].ToString();
                    cx.TRONGTAI=int.Parse(sa["TRONGTAI"].ToString());
                    cx.Tthientai=int.Parse(sa["TTHIENTAI"].ToString());
                    lst.Add(cx);
                }
                sa.Dispose();
                tran.Commit();
                command.Dispose();
                conn.Close();
                conn.Dispose();
                return lst;

            }
            #endregion
         #region usp_tinchuyentheobendi
            public static List<TongHopDTO> SelectSoLuongChuyentheobendi(String bendi, ref int tong)
            {
                List<TongHopDTO> lst = new List<TongHopDTO>();

                SqlTransaction tran;
                SqlConnection conn = DataProvider.ConnectDB();
                SqlCommand command = conn.CreateCommand(); //tao doi tuong command
                tran = conn.BeginTransaction(IsolationLevel.ReadCommitted, "ChuyenXetheobendi");//start tran tren connection nay
                command.Connection = conn;
                command.Transaction = tran;

                command.CommandTimeout = 2000;
                //ma chuyen ton tai?
                command.CommandText = "Select ld.MACHUYEN from LICHDI ld, CHUYENXE cx where ld.MACHUYEN=cx.MACHUYEN and cx.BENXEDI=@bendi";
                SqlParameter pa = new SqlParameter("@bendi", SqlDbType.VarChar);
                pa.Value = bendi;
                command.Parameters.Add(pa);
                SqlDataReader rd = command.ExecuteReader();
                if (rd.HasRows == false)
                {
                    rd.Close();
                    tran.Rollback();
                    throw new Exception("Không có chuyến xe nào bắt đầu xuất phát từ bến vừa ch");
                }
                command.Parameters.Clear();
                rd.Dispose();
                command.Dispose();

                command.CommandText = "select count(ld.MACHUYEN) from LICHDI ld, CHUYENXE cx where ld.MACHUYEN=cx.MACHUYEN and cx.BENXEDI=@bendi";
                command.Parameters.Add(pa);
                int tt = (int)command.ExecuteScalar();//gia tri output
                tong = tt;
                System.Threading.Thread.Sleep(8000);
                rd.Dispose();
                command.Dispose();
                command.Parameters.Clear();
                command.CommandText = " select ld.NGAYDI,cx.GIODI,cx.GIODEN,cx.BENXEDI,cx.BENXEDEN,ld.MALOAI,ld.SOHIEU,lx.TRONGTAI,ld.TTHIENTAI from LICHDI ld, CHUYENXE cx, LOAIXE lx where cx.MACHUYEN=ld.MACHUYEN and ld.MALOAI=lx.MALOAI and cx.BENXEDI=@bendi";
                command.Parameters.Add(pa);
                SqlDataReader sa = command.ExecuteReader();
                while (sa.Read())
                {
                    TongHopDTO cx = new TongHopDTO();
                    cx.Ngaydi = DateTime.Parse(sa["ngaydi"].ToString());
                    cx.GIODI = DateTime.Parse(sa["giodi"].ToString());
                    cx.GIODEN = DateTime.Parse(sa["gioden"].ToString());
                    cx.BENXEDI = sa["BENXEDI"].ToString();
                    cx.BENXEDEN = sa["BENXEDEN"].ToString();
                    cx.Maloai = sa["MALOAI"].ToString();
                    cx.Sohieu = sa["sohieu"].ToString();
                    cx.TRONGTAI = int.Parse(sa["TRONGTAI"].ToString());
                    cx.Tthientai = int.Parse(sa["TTHIENTAI"].ToString());
                    lst.Add(cx);
                }
                sa.Dispose();
                tran.Commit();
                command.Dispose();
                conn.Close();
                conn.Dispose();
                return lst;

            }
            #endregion
         #region usp_tinchuyentheobenden
            public static List<TongHopDTO> SelectSoLuongChuyentheobenden(String benden, ref int tong)
            {
                List<TongHopDTO> lst = new List<TongHopDTO>();

                SqlTransaction tran;
                SqlConnection conn = DataProvider.ConnectDB();
                SqlCommand command = conn.CreateCommand(); //tao doi tuong command
                tran = conn.BeginTransaction(IsolationLevel.ReadCommitted, "ChuyenXetheobenden");//start tran tren connection nay
                command.Connection = conn;
                command.Transaction = tran;

                command.CommandTimeout = 2000;
                //ma chuyen ton tai?
                command.CommandText = "Select * from LICHDI ld, CHUYENXE cx where ld.MACHUYEN=cx.MACHUYEN and cx.BENXEDEN=@benden";
                SqlParameter pa = new SqlParameter("@benden", SqlDbType.VarChar);
                pa.Value = benden;
                command.Parameters.Add(pa);
                SqlDataReader rd = command.ExecuteReader();
                if (rd.HasRows == false)
                {
                    rd.Close();
                    tran.Rollback();
                    throw new Exception("Không có chuyến xe nào đến bến vừa chon");
                }
                command.Parameters.Clear();
                rd.Dispose();
                command.Dispose();

                command.CommandText = "select count(ld.MACHUYEN) from LICHDI ld, CHUYENXE cx where ld.MACHUYEN=cx.MACHUYEN and cx.BENXEDEN=@benden";
                command.Parameters.Add(pa);
                int tt = (int)command.ExecuteScalar();//gia tri output
                tong = tt;
                System.Threading.Thread.Sleep(8000);
                rd.Dispose();
                command.Dispose();
                command.Parameters.Clear();
                command.CommandText = " select ld.NGAYDI,cx.GIODI,cx.GIODEN,cx.BENXEDI,cx.BENXEDEN,ld.MALOAI,ld.SOHIEU,lx.TRONGTAI,ld.TTHIENTAI from LICHDI ld, CHUYENXE cx, LOAIXE lx where cx.MACHUYEN=ld.MACHUYEN and ld.MALOAI=lx.MALOAI and cx.BENXEDEN=@benden";
                command.Parameters.Add(pa);
                SqlDataReader sa = command.ExecuteReader();
                while (sa.Read())
                {
                    TongHopDTO cx = new TongHopDTO();
                    cx.Ngaydi = DateTime.Parse(sa["ngaydi"].ToString());
                    cx.GIODI = DateTime.Parse(sa["giodi"].ToString());
                    cx.GIODEN = DateTime.Parse(sa["gioden"].ToString());
                    cx.BENXEDI = sa["BENXEDI"].ToString();
                    cx.BENXEDEN = sa["BENXEDEN"].ToString();
                    cx.Maloai = sa["MALOAI"].ToString();
                    cx.Sohieu = sa["sohieu"].ToString();
                    cx.TRONGTAI = int.Parse(sa["TRONGTAI"].ToString());
                    cx.Tthientai = int.Parse(sa["TTHIENTAI"].ToString());
                    lst.Add(cx);
                }
                sa.Dispose();
                tran.Commit();
                command.Dispose();
                conn.Close();
                conn.Dispose();
                return lst;

            }
            #endregion
         #region usp_tinchuyentheotrongtai
            public static List<TongHopDTO> SelectSoLuongChuyentheotrongluong(int bdkg,int ktkg ,ref int tong)
            {
                List<TongHopDTO> lst = new List<TongHopDTO>();

                SqlTransaction tran;
                SqlConnection conn = DataProvider.ConnectDB();
                SqlCommand command = conn.CreateCommand(); //tao doi tuong command
                tran = conn.BeginTransaction(IsolationLevel.ReadCommitted, "ChuyenXetheotrongtai");//start tran tren connection nay
                command.Connection = conn;
                command.Transaction = tran;

                command.CommandTimeout = 2000;
                //ma chuyen ton tai?
                command.CommandText = "Select * from LICHDI ld, LOAIXE lx where ld.MALOAI=lx.MALOAI and @bdkg <= lx.TRONGTAI and @ktkg>=TRONGTAI";
                SqlParameter pa = new SqlParameter("@bdkg", SqlDbType.Int);
                SqlParameter pa1 = new SqlParameter("@ktkg", SqlDbType.Int);
                pa.Value = bdkg;
                pa1.Value = ktkg;
                command.Parameters.Add(pa);
                command.Parameters.Add(pa1);
                SqlDataReader rd = command.ExecuteReader();
                if (rd.HasRows == false)
                {
                    rd.Close();
                    tran.Rollback();
                    throw new Exception("Không có chuyến xe nào có trọng tải phù hợp");
                }
                command.Parameters.Clear();
                rd.Dispose();
                command.Dispose();

                command.CommandText = "select count(ld.MACHUYEN) from LICHDI ld, LOAIXE lx where ld.MALOAI=lx.MALOAI and lx.TRONGTAI>=@bdkg and lx.TRONGTAI <=@ktkg";
                command.Parameters.Add(pa);
                command.Parameters.Add(pa1);
                int tt = (int)command.ExecuteScalar();//gia tri output
                tong = tt;
                 System.Threading.Thread.Sleep(8000);
                rd.Dispose();
                command.Dispose();
                command.Parameters.Clear();
                command.CommandText = " select ld.NGAYDI,cx.GIODI,cx.GIODEN,cx.BENXEDI,cx.BENXEDEN,ld.MALOAI,ld.SOHIEU,lx.TRONGTAI,ld.TTHIENTAI from LICHDI ld, CHUYENXE cx, LOAIXE lx where cx.MACHUYEN=ld.MACHUYEN and ld.MALOAI=lx.MALOAI and   lx.TRONGTAI>=@bdkg and lx.TRONGTAI <=@ktkg";
                command.Parameters.Add(pa);
                command.Parameters.Add(pa1);
                SqlDataReader sa = command.ExecuteReader();
                while (sa.Read())
                {
                    TongHopDTO cx = new TongHopDTO();
                    cx.Ngaydi = DateTime.Parse(sa["ngaydi"].ToString());
                    cx.GIODI = DateTime.Parse(sa["giodi"].ToString());
                    cx.GIODEN = DateTime.Parse(sa["gioden"].ToString());
                    cx.BENXEDI = sa["BENXEDI"].ToString();
                    cx.BENXEDEN = sa["BENXEDEN"].ToString();
                    cx.Maloai = sa["MALOAI"].ToString();
                    cx.Sohieu = sa["sohieu"].ToString();
                    cx.TRONGTAI = int.Parse(sa["TRONGTAI"].ToString());
                    cx.Tthientai = int.Parse(sa["TTHIENTAI"].ToString());
                    lst.Add(cx);
                }
                sa.Dispose();
                tran.Commit();
                command.Dispose();
                conn.Close();
                conn.Dispose();
                return lst;

            }
            #endregion
    }
}
